IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_MediaContentFindByKeywords]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_MediaContentFindByKeywords]
GO

create procedure [dbo].[cms_sp_MediaContentFindByKeywords]
(@SearchQuery varchar(max),
 @IsInAdministrationArea bit = null,
 @SiteGroupId int = null,
 @SiteId int = null,
 @MediaId int = null
)
AS

declare @Searched table 
(
	MediaId int not null	
);

WITH  tree
        AS (
            SELECT  MediaId, ParentMediaId
            FROM    Media
            WHERE   MediaId = @MediaId or (@MediaId is null and ParentMediaId IS NULL) 
            UNION ALL
              --recursive member
            SELECT  t.MediaId, t.ParentMediaId
            FROM    Media t
                    JOIN tree as a
                      ON t.ParentMediaId = a.MediaId
           )
insert into @Searched (MediaId)
 select MediaId FROM tree

select M.[MediaContentId] as [Id]
      ,M.[MediaId]
      ,M.[SiteId]
      ,M.[SiteGroupId]
      ,M.[MetaCode]
      ,M.[Description]
      ,M.[CreatedBy]
      ,M.[DateCreated]
      ,M.[LastUpdatedBy]
      ,M.[DateLastUpdated]
      ,[MediaContentName]
	  ,MediaFiles.MediaFileId as MainFile_Id
	  ,IsPrimary as MainFile_IsPrimary
	  ,MimeTypes.MimeTypeId as MainFile_MimeTypeId
	  ,MimeTypes.MimeTypeId as MainFile_MimeType_Id
	  ,ContentType as MainFile_MimeType_ContentType
	  ,MimeTypeName as MainFile_MimeType_MimeTypeName
	  ,IsImage as MainFile_MimeType_IsImage
	  ,LinkedFileName as MainFile_LinkedFileName
	  ,OriginalFileName as MainFile_OriginalFileName
	  ,FileSize as MainFile_FileSize
	  ,Height as MainFile_Height
	  ,Width as MainFile_Width
  from [dbo].MediaContents M join @Searched s on M.MediaId = S.MediaId 
       join dbo.MediaContentFileLinks
       on MediaContentFileLinks.MediaContentId = M.MediaContentId and IsPrimary=1
	   join dbo.MediaFiles on MediaContentFileLinks.MediaFileId = MediaFiles.MediaFileId
	   left join dbo.MimeTypes
		on MimeTypes.MimeTypeId = MediaFiles.MimeTypeId
	 WHERE ((M.SiteGroupId is null and M.SiteId is null) or 
		(M.SiteGroupId = @SiteGroupId and M.SiteId is null) or
		(M.SiteGroupId = @SiteGroupId and M.SiteId = @SiteId)) and 
		M.MediaContentName like '%'+@searchQuery+'%'

 GO